﻿using System;
using System.IO;
using System.Threading;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;

namespace rebides
{
    public class CSV2SQL
    {
        // thread para a importação de CSVs
        private static Thread converter;
        // Lista com todos os professores
        public static List<Professor> Professors;
        // para não importar mais que um ficheiro de cada vez
        private static bool status { get; set; }
        // para as ligações SQLite
        private SQLiteConnection Conn;
        private SQLiteCommand Cmd;
        // nome do ficheiro a importar
        private string fileName { get; set; }
        

        /// <summary>
        /// Construtor. Inicia a construção da estrutura de dados
        /// </summary>
        public CSV2SQL()
        {
            this.buildData();
        }


        /// <summary>
        /// Iniciar o processo de conversão do ficheiro CSV
        /// </summary>
        /// <param name="fileName"></param>
        public void convert(string fileName)
        {
            this.fileName = fileName;
            this.start();
        }


        /// <summary>
        /// Importar CSV (thread)
        /// </summary>
        private void start()
        {
            if (!CSV2SQL.status)
            {
                CSV2SQL.status = true; // assinalar thread a correr
                CSV2SQL.converter = new Thread(new ThreadStart(this.import));
                CSV2SQL.converter.Start();
            }
            else
                rebides.alert("Importar CSV",
                    "Já está uma operação em curso, por favor aguarde.");
        } // end start()

        /// <summary>
        /// importar csv para a base de dados
        /// </summary>
        private void import()
        {
            string fileName = this.fileName;

            this.Conn = new SQLiteConnection();
            this.Conn.ConnectionString = "Data Source=rebides.db3;" +
                                         "New=True;Compress=True;" +
                                         "Synchronous=Off";
            this.Conn.Open();
            this.Cmd = new SQLiteCommand();
            this.Cmd = Conn.CreateCommand();

            this.Cmd.CommandText = "CREATE TABLE IF NOT EXISTS teachers" +
                                    " (id TEXT, nome TEXT, grau TEXT, " +
                                    " curso TEXT, categoria TEXT, " +
                                    "regime TEXT, ano TEXT, " +
                                    "tipo_estabelecimento TEXT, " +
                                    "nome_estabelecimento TEXT)";
            this.Cmd.ExecuteNonQuery();

            using (StreamReader conteudo = new StreamReader(@fileName))
            {
                // ler ficheiro todo
                string ficheiro = conteudo.ReadToEnd();

                // retirar LF
                ficheiro = ficheiro.Replace((char)10, (char)32);
                // substituir virgulas por LF 
                // que passa a ser o separador dos campos
                ficheiro = ficheiro.Replace((char)44, (char)10);

                string[] temp = ficheiro.Split('"');
                for (int i = 0; i < temp.Length; i++)
                {
                    if (i % 2 == 1)
                    {
                        // substituir CR por espaço
                        temp[i] = temp[i].Replace((char)13, (char)32);
                        // os LF dentro das aspas voltam a ser virgulas 
                        // pois pertencem ao mesmo campo
                        temp[i] = temp[i].Replace((char)10, (char)44);
                    }
                }

                ficheiro = String.Join("", temp);
                // partir linhas por CR
                temp = ficheiro.Split((char)13);

                for (int i = 1; i < temp.Length; i++)
                {
                    // partir colunas por LF
                    string[] filter = temp[i].Split((char)10);

                    try
                    {
                        this.Cmd.CommandText =
                            String.Format("INSERT OR REPLACE into teachers " +
                                          "(id, nome, grau, curso, " +
                                          "categoria, regime, ano, " +
                                          "tipo_estabelecimento, " +
                                          "nome_estabelecimento) " +
                                          "VALUES (\"{0}\", \"{1}\", " +
                                          "\"{2}\", \"{3}\", \"{4}\"" +
                                          ", \"{5}\", {6}, \"{7}\", \"{8}\")",
                                          filter[0], filter[1], filter[2],
                                          filter[3], filter[4], filter[5],
                                          filter[6], filter[7], filter[8]);
                        this.Cmd.ExecuteNonQuery();
                    }
                    catch (System.Data.SQLite.SQLiteException erro)
                    {
                        Console.WriteLine(i + ", " + erro);
                    }
                    catch
                    {
                        // se nao entrou entrasse...
                        Console.WriteLine(String.Join(",", filter));
                    }
                }
            }

            this.Conn.Close();
            
            // alertar operação concluída
            rebides.alert("Importar CSV", "Importação concluída!");
            CSV2SQL.status = false; // repor status
        }

        /// <summary>
        /// Terminar threads
        /// </summary>
        public static void cancel()
        {
            if (CSV2SQL.status)
                CSV2SQL.converter.Abort();
        }

        /// <summary>
        /// Lista de anos
        /// </summary>
        /// <returns>Lista de strings</returns>
        public List<string> getAnosList()
        {
            var ano = from r in Professors
                      orderby r.ano descending
                      select r.ano;
            return ano.Distinct().ToList();
        }

        /// <summary>
        /// Lista de Escolas
        /// </summary>
        /// <returns>Lista de Strings</returns>
        public List<string> getEscolasList()
        {
            var est = from e in Professors
                      orderby e.nome_estabelecimento ascending
                      select e.nome_estabelecimento;
            return est.Distinct().ToList();
        }

        /// <summary>
        /// obter dados por categoria
        /// </summary>
        /// <param name="escola"></param>
        /// <param name="ano"></param>
        /// <returns>Lista de strings</returns>
        public List<string> getByCategoria(string escola, string ano)
        {
            List<string> output = new List<string>();

            var categorias =    from c in Professors
                                where c.ano == ano
                                && c.nome_estabelecimento == escola
                                orderby c.categoria ascending
                                select c.categoria;

            output.Add(String.Format("Por Categoria\n {0} em {1}:\n\n",
                                           escola, ano));

            foreach (string categoria in categorias.Distinct())
            {
                var counts = from c in Professors
                             where c.ano == ano
                             && c.nome_estabelecimento == escola
                             && c.categoria == categoria
                             select c.categoria;

                output.Add(String.Format("\x09{0}: {1}\n",
                                               categoria, counts.Count())
                                 );
            } // end foreach
            return output;
        } // end getByCategoria

        /// <summary>
        /// obter dados por grau
        /// </summary>
        /// <param name="escola"></param>
        /// <param name="ano"></param>
        /// <returns>lista de strings</returns>
        public List<string> getByGrau(string escola, string ano)
        {
            List<string> output = new List<string>();

            var graus = from g in Professors
                        where g.ano == ano
                        && g.nome_estabelecimento == escola
                        orderby g.ultimo_grau ascending
                        select g.ultimo_grau;

            output.Add(String.Format("Por Grau\n {0} em {1}:\n\n",
                       escola, ano));

            foreach (string grau in graus.Distinct())
            {
                var counts = from c in Professors
                             where c.ano == ano
                             && c.nome_estabelecimento == escola
                             && c.ultimo_grau == grau
                             select c.ultimo_grau;

                output.Add(String.Format("\x09{0}: {1}\n",
                           grau, counts.Count())
                          );
            } // end foreach
            return output;
        } // end getByGrau

        /// <summary>
        /// Construir dados
        /// </summary>
        public void buildData()
        {
            try
            {
                Professors = new List<Professor>();
                SQLiteConnection Conn = new SQLiteConnection();
                Conn.ConnectionString = "Data Source=rebides.db3;" +
                                        "New=True;Compress=True;" +
                                        "Synchronous=Off";
                Conn.Open();
                SQLiteCommand Cmd = new SQLiteCommand();
                Cmd = Conn.CreateCommand();

                Cmd.CommandText = "SELECT * FROM teachers";
                SQLiteDataReader sqReader = Cmd.ExecuteReader();
                while (sqReader.Read())
                {
                    Professors.Add(
                    new Professor(sqReader[0].ToString(),
                                  sqReader[1].ToString(),
                                  sqReader[2].ToString(),
                                  sqReader[3].ToString(),
                                  sqReader[4].ToString(),
                                  sqReader[5].ToString(),
                                  sqReader[6].ToString(),
                                  sqReader[7].ToString(),
                                  sqReader[8].ToString()
                                 ) // end new Professor
                     ); // end Add()
                } // end while
            }
            catch
            {
                rebides.alert("Erro na base de dados",
                              "Deve importar um ficheiro");
            }
        } // end buildData()
    }
}